package com.lagou.dao.impl;

import com.lagou.dao.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 课程内容管理DAO层实现类
 */
public class CourseContentDapImpl implements CourseContentDao {
    //根据课程Id查询课程相关信息
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT \n" +
                    "id, \n" +
                    "course_id, \n" +
                    "section_name,\n" +
                    "description, \n" +
                    "order_num,STATUS FROM course_section cs WHERE course_id = ?";
            List<Course_Section> sectionList = qr.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);
            //遍历List<Course_Section>，往Course_Section里List<Course_Lesson>集合添加值
            for (Course_Section c:
                 sectionList) {
                //根据章节id查找课时信息
                List<Course_Lesson> courseLessons = findLessonBySectionId(c.getId());
                c.setCourseLessons(courseLessons);
            }
            return sectionList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //根据章节id查找课时信息
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT \n" +
                    "id, \n" +
                    "course_id, \n" +
                    "section_id, \n" +
                    "theme,\n" +
                    "duration, \n" +
                    "is_free, \n" +
                    "order_num,\n" +
                    "STATUS\n" +
                    "FROM course_lesson WHERE section_id = ?";
            List<Course_Lesson> courseLessons = qr.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);
            return courseLessons;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 根据课程id寻找课程
     * @param courseId
     * @return
     */
    @Override
    public Course findCourseByCourseId(int courseId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT id,course_name FROM course WHERE id = ?";
            Course course = qr.query(sql, new BeanHandler<Course>(Course.class), courseId);
            return course;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    /**
     * 保存章节信息
     * @param section
     * @return
     */
    @Override
    public int saveSection(Course_Section section) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "insert into course_section(course_id,section_name,description,order_num,STATUS,create_time,update_time) values(?,?,?,?,?,?,?)";
            Object[] param = {section.getCourse_id(),section.getSection_name(),section.getDescription(), section.getOrder_num(),section.getStatus(),section.getCreate_time(),section.getUpdate_time()};
            int update = qr.update(sql, param);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }
    }
    //修改章节的代码
    @Override
    public int updateSection(Course_Section section) {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "UPDATE course_section SET section_name = ?, description = ? , order_num = ?  ,update_time = ? WHERE id = ?;";
        Object[] param = {section.getSection_name(),section.getDescription(),section.getOrder_num(), section.getUpdate_time(),section.getId()};
        try {
            int update = qr.update(sql, param);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }
    }

    /**
     * 更新章节状态
     * @param id
     * @param status
     * @return
     */
    @Override
    public int updateSectionStatus(int id, int status) {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "UPDATE course_section SET STATUS = ?,update_time = ? WHERE id = ?;";
        Object[] param = {status , DateUtils.getDateFormart(),id};
        try {
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
    //保存课时方法
    @Override
    public int saveLesson(Course_Lesson lesson) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "insert into course_lesson(course_id,section_id,theme,duration,is_free,order_num,create_time,update_time) values(?,?,?,?,?,?,?,?)";
            Object[] params = {lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),lesson.getDuration(),
                    lesson.getIs_free(),lesson.getOrderNum(),lesson.getCreate_time(),lesson.getUpdate_time()};
            int update = qr.update(sql, params);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }
    }

    /**
     * 跟新课时方法
     * @param lesson
     * @return
     */
    @Override
    public int updateLesson(Course_Lesson lesson) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "UPDATE course_lesson SET \n" +
                    "course_id=?,\n" +
                    "section_id=?,\n" +
                    "theme=?,\n" +
                    "duration=?,\n" +
                    "is_free=?,\n" +
                    "order_num=?,\n" +
                    "update_time=?\n" +
                    "WHERE id=?;";
            Object[] params = {lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),lesson.getDuration(),
                    lesson.getIs_free(),lesson.getOrderNum(),lesson.getUpdate_time(),lesson.getId()};
            int update = qr.update(sql, params);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }
    }
}
